from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# 用户
class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    password = db.Column(db.String(200), nullable=False)

    # 书架关系
    bookshelves = db.relationship('Bookshelf', back_populates='user')
    # 阅读进度
    reading_progresses = db.relationship('ReadingProgress', back_populates='user')
    # 评论/评分
    reviews = db.relationship('Review', back_populates='user')
    # 用户阅读偏好，一对一
    preference = db.relationship('UserPreference', back_populates='user', uselist=False)

# 书籍
class Book(db.Model):
    __tablename__ = 'book'
    id = db.Column(db.Integer, primary_key=True)  
    title = db.Column(db.String(200), nullable=False)
    author = db.Column(db.String(100), nullable=True) 
    image = db.Column(db.String(200), nullable=True)  
    description = db.Column(db.Text, nullable=True)
    tags = db.Column(db.JSON, nullable=True)  
    epub_path = db.Column(db.String(200), nullable=True) 

    # 多对多：书籍 - 书架
    bookshelves = db.relationship('Bookshelf', secondary='bookshelf_book', back_populates='books')
    # 一对多：书籍 - 阅读进度
    reading_progresses = db.relationship('ReadingProgress', back_populates='book')
    # 一对多：书籍 - 评论
    reviews = db.relationship('Review', back_populates='book')

# 书柜
class Bookshelf(db.Model):
    __tablename__ = 'bookshelf'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    user = db.relationship('User', back_populates='bookshelves')

    # 多对多：书柜 - 书籍
    books = db.relationship('Book', secondary='bookshelf_book', back_populates='bookshelves')

# 中间表：书柜与书籍的多对多关系
bookshelf_book = db.Table(
    'bookshelf_book',
    db.Column('bookshelf_id', db.Integer, db.ForeignKey('bookshelf.id'), primary_key=True),
    db.Column('book_id', db.Integer, db.ForeignKey('book.id'), primary_key=True)
)

# 阅读进度表
class ReadingProgress(db.Model):
    __tablename__ = 'reading_progress'
    id = db.Column(db.Integer, primary_key=True)
    
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    user = db.relationship('User', back_populates='reading_progresses')

    book_id = db.Column(db.Integer, db.ForeignKey('book.id'), nullable=False)
    book = db.relationship('Book', back_populates='reading_progresses')

    # 百分比
    progress_percentage = db.Column(db.Float, nullable=True, default=0.0)
    # EPUB CFI
    cfi = db.Column(db.String(500), nullable=True)

    updated_at = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp())

# 评论/评分表
class Review(db.Model):
    __tablename__ = 'review'
    id = db.Column(db.Integer, primary_key=True)

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    user = db.relationship('User', back_populates='reviews')

    book_id = db.Column(db.Integer, db.ForeignKey('book.id'), nullable=False)
    book = db.relationship('Book', back_populates='reviews')

    rating = db.Column(db.Integer, nullable=True)
    comment = db.Column(db.Text, nullable=True)

    created_at = db.Column(db.DateTime, default=db.func.current_timestamp())

# 用户阅读偏好表
class UserPreference(db.Model):
    __tablename__ = 'user_preference'
    id = db.Column(db.Integer, primary_key=True)

    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False, unique=True)
    user = db.relationship('User', back_populates='preference')

    background_color = db.Column(db.String(20), default='#ffffff')
    text_color = db.Column(db.String(20), default='#000000')
    font_size = db.Column(db.Integer, default=16)
    letter_spacing = db.Column(db.Float, default=0)
    line_height = db.Column(db.Float, default=1.5)
    flip_mode = db.Column(db.String(10), default='both')
